package com.company.project.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.company.project.common.utils.ExcelTemplate;
import com.company.project.common.utils.FileUtil;
import com.company.project.entity.RptAnnualReportEntity;
import com.company.project.entity.RptStatisticsReportEntity;
import com.company.project.mapper.RptAnnualHistoryReportMapper;
import com.company.project.mapper.RptAnnualReportMapper;
import com.company.project.service.RptAnnualReportService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;


@Service
@Slf4j
public class RptAnnualReportServiceImpl extends ServiceImpl<RptAnnualReportMapper, RptAnnualReportEntity> implements RptAnnualReportService {

    @Autowired
    RptAnnualReportMapper rptAnnualReportMapper;

    @Autowired
    RptAnnualHistoryReportMapper rptAnnualHistoryReportMapper;

    /**
     * 查询工商报表
     *
     * @param rptAnnualReportEntity
     * @return
     */
    @Override
    public PageInfo<RptAnnualReportEntity> pageInfo(RptAnnualReportEntity rptAnnualReportEntity) {

        PageHelper.startPage(rptAnnualReportEntity.getPage(), rptAnnualReportEntity.getLimit());
        List<RptAnnualReportEntity> list = rptAnnualReportMapper.findAllAnnualReport(rptAnnualReportEntity);
        PageInfo<RptAnnualReportEntity> pageInfo = new PageInfo<>(list);
        return pageInfo;
    }

    /**
     * 通过社会信用代码查询数据库是否已存在该数据
     *
     * @param socialCreditCode
     * @return
     */
    @Override
    public RptAnnualReportEntity findSocialCreditCodeIsHave(String socialCreditCode) {

        return rptAnnualReportMapper.findSocialCreditCodeIsHave(socialCreditCode);
    }

    /**
     * 通过社会信用代码查询数据库中该条数据是否一致
     *
     * @param rptAnnualReportEntity
     * @return
     */
    @Override
    public RptAnnualReportEntity findIdenticalBySocialCreditCode(RptAnnualReportEntity rptAnnualReportEntity) {

        return rptAnnualReportMapper.findIdenticalBySocialCreditCode(rptAnnualReportEntity);
    }

    /**
     * 添加工商报表
     *
     * @param rptAnnualReportEntityList
     * @return
     */
    @Override
    public Integer addAnnualReport(List<RptAnnualReportEntity> rptAnnualReportEntityList) {

        rptAnnualReportMapper.addAnnualReport(rptAnnualReportEntityList);

        return 1;
    }

    /**
     * 修改工商报表
     *
     * @param rptAnnualReportEntityList
     * @return
     */
    @Override
    public Integer updateAnnualReport(List<RptAnnualReportEntity> rptAnnualReportEntityList) {

        for (RptAnnualReportEntity rptAnnualReportEntity : rptAnnualReportEntityList) {
            rptAnnualReportMapper.updateAnnualReport(rptAnnualReportEntity);
        }
        return 1;
    }

    /**
     * 自动在凌晨的时候将当天的工商报表数据存入到历史表中
     */
    @Scheduled(cron = "0 59 23 * * ? ")
    public void addAnnualHistoryReport() {

        //查询工商报表当天的所有数据
        List<RptAnnualReportEntity> rptAnnualReportEntityList = rptAnnualReportMapper.findAll();
        //如果工商报表当天的所有数据存在,就存入历史表中,并且删除当天的数据
        if (rptAnnualReportEntityList.size() > 0) {
            //删除工商报表所有数据
            rptAnnualReportMapper.delAnnualReport();
            //添加工商历史报表
            rptAnnualHistoryReportMapper.addAnnualHistoryReport(rptAnnualReportEntityList);
        }
    }

    /**
     * 工商报表Excel上传
     *
     * @param upload
     * @param reportStatus
     * @return
     * @throws IOException
     */
    @Override
    public List<RptAnnualReportEntity> annualReportUploadExcel(MultipartFile upload, Integer reportStatus) throws IOException {

        InputStream inputStream = upload.getInputStream();
        List<RptAnnualReportEntity> rptAnnualReportEntityList = new ArrayList<>();

        //获取工作部
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
        //获取工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);

        int lastRowNum = sheet.getLastRowNum();
        for (int i = 2; i <= lastRowNum; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row != null) {
                List<String> list = new ArrayList<>();
                //循环遍历每行的单元格
                for (Cell cell : row) {
                    if (cell != null) {
                        String date = "";
                        String value = "";
                        //判断单元格的类型是不是数字类型
                        if (cell.getCellType() == CellType.NUMERIC) {
                            //判断单元格的类型是不是日期类型
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                date = simpleDateFormat.format(cell.getDateCellValue());
                            } else {
                                cell.setCellType(CellType.STRING);
                                value = cell.getStringCellValue();
                            }
                        } else {
                            cell.setCellType(CellType.STRING);
                            value = cell.getStringCellValue();
                        }
                        if (value != null && !"".equals(value)) {
                            list.add(value);
                        } else if (date != null && !"".equals(date)) {
                            list.add(date);
                        }

                    }
                }
                if (list.size() > 0) {
                    RptAnnualReportEntity rptAnnualReportEntity = new RptAnnualReportEntity();

                    String enterpriseName = list.get(1);
                    rptAnnualReportEntity.setEnterpriseName(enterpriseName);//获取企业名称
                    rptAnnualReportEntity.setSocialCreditCode(list.get(2));//获取统一社会信用代码
                    rptAnnualReportEntity.setRegistrationNumber(list.get(3));//获取注册号
                    rptAnnualReportEntity.setRegistrationAuthority(list.get(4));//获取登录机关
                    rptAnnualReportEntity.setDomination(list.get(5));//获取管辖机关
                    rptAnnualReportEntity.setAddress(list.get(6));//获取地址
                    rptAnnualReportEntity.setContactNumber(list.get(7));//获取联系电话
                    rptAnnualReportEntity.setEstablishDate(list.get(8));//获取成立日期
                    rptAnnualReportEntity.setApprovalDate(list.get(9));//获取核准日期
                    rptAnnualReportEntity.setEnterpriseStatus(list.get(10));//获取企业状态
                    rptAnnualReportEntity.setLiaisonManName(list.get(11));//获取联络员姓名
                    rptAnnualReportEntity.setLiaisonManPhone(list.get(12));//获取联络员电话
                    SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    String crateTime = simpleDateFormat.format(new Date());
                    rptAnnualReportEntity.setCreateTime(crateTime);//获取当前时间
                    //判断企业类型
                    int enterpriseType;
                    if (enterpriseName.contains("农合社")) {
                        enterpriseType = 2;
                    } else if (enterpriseName.contains("个人")) {
                        enterpriseType = 3;
                    } else {
                        enterpriseType = 1;
                    }
                    rptAnnualReportEntity.setEnterpriseType(enterpriseType);//获取企业类型
                    int matching = 2;
                    rptAnnualReportEntity.setMatching(matching);//默认未匹配
                    rptAnnualReportEntity.setReportStatus(reportStatus);//获取上报状态

                    //添加到集合
                    rptAnnualReportEntityList.add(rptAnnualReportEntity);
                }
            }
        }

        return rptAnnualReportEntityList;
    }

    //工商报表Excel下载
    @Override
    public Integer annualReportExcel(String title, List<RptAnnualReportEntity> rptAnnualReportEntityList, HttpServletResponse response) {

        try {
            //文件名称
            String fileName = title + ".xlsx";

            //类型转换
            List<Map<String, Object>> list = new ArrayList<>();
            for (RptAnnualReportEntity rptAnnualReportEntity : rptAnnualReportEntityList) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", rptAnnualReportEntity.getId());
                map.put("enterprise_name", rptAnnualReportEntity.getEnterpriseName());
                map.put("social_credit_code", rptAnnualReportEntity.getSocialCreditCode());
                map.put("registration_number", rptAnnualReportEntity.getRegistrationNumber());
                map.put("registration_authority", rptAnnualReportEntity.getRegistrationAuthority());
                map.put("domination", rptAnnualReportEntity.getDomination());
                map.put("address", rptAnnualReportEntity.getAddress());
                map.put("contact_number", rptAnnualReportEntity.getContactNumber());
                map.put("establish_date", rptAnnualReportEntity.getEstablishDate());
                map.put("approval_date", rptAnnualReportEntity.getApprovalDate());
                map.put("enterprise_status", rptAnnualReportEntity.getEnterpriseStatus());
                map.put("liaison_man_name", rptAnnualReportEntity.getLiaisonManName());
                map.put("liaison_man_phone", rptAnnualReportEntity.getLiaisonManPhone());
                map.put("create_time", rptAnnualReportEntity.getCreateTime());
                map.put("report_status", rptAnnualReportEntity.getReportStatus());
                map.put("enterprise_type", rptAnnualReportEntity.getEnterpriseType());
                map.put("matching", rptAnnualReportEntity.getMatching());

                list.add(map);
            }

            if (list != null && list.size() > 0) {
                //获取文件夹路径
                ClassPathResource resource = new ClassPathResource("exceltemplate/annualreport.xlsx");
                InputStream inputStream = resource.getInputStream();
                ExcelTemplate excel = new ExcelTemplate(inputStream);

                //替换模板row区域的${}值
                LinkedHashMap<Integer, LinkedList<String>> rows = linkedHashMapRows(list);
                excel.addRowByExist(0, 2, 2, 3, rows, true);
                excel.save(response, fileName);
                log.info("导出成功！");
            }
            return 1;
        } catch (Exception e) {
            log.error("导出失败！");
            e.printStackTrace();
            return 0;
        }
    }

    public void excelFile(HttpServletResponse response) throws IOException {
        ClassPathResource resource = new ClassPathResource("exceltemplate/annualreport.xlsx");
        InputStream bis = resource.getInputStream();
        String fileName = "张三.xls";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("multipart/form-data");
        BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
        int len = 0;
        while ((len = bis.read()) != -1) {
            out.write(len);
            out.flush();
        }
        out.close();
        bis.close();
    }


    @Override
    public void exportInfo(String title, List<RptStatisticsReportEntity> list, HttpServletResponse response) throws IOException {
        OutputStream out = null;
        BufferedOutputStream bos = null;
        try {
            String templateFileName = FileUtil.getPath() + "exceltemplate" + File.separator + "test.xlsx";

            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(title + ".xlsx", "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "UTF-8"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);

            //读取Excel
            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(templateFileName).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            //customerDealerInfo 是我查询并需导出的数据，并且里面的字段和excel需要导出的字段对应
            // 直接写入Excel数据

//            out = response.getOutputStream();
//            new BufferedOutputStream(new FileOutputStream());
            excelWriter.fill(list, writeSheet);
            excelWriter.finish();
            bos.flush();

        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>(16);
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

    @Override
    public List<RptAnnualReportEntity> findAll() {
        List<RptAnnualReportEntity> list = rptAnnualReportMapper.findAllAnnualReport(new RptAnnualReportEntity());
        return list;
    }

    public void exportInfo0(String title, List<RptAnnualReportEntity> rptAnnualReportEntityList, HttpServletResponse response) throws IOException {

        OutputStream out = null;
        BufferedOutputStream bos = null;
        try {
            String templateFileName = FileUtil.getPath() + "templates" + File.separator + "street.xlsx";

            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(title + ".xls", "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);

            //读取Excel
            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(templateFileName).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            //list map 是查询并需导出的数据，并且里面的字段和excel需要导出的字段对应
            // 直接写入Excel数据
            //List<Map> list = rptAnnualReportEntityList;
            List<Map<String, Object>> list = new ArrayList<>();
            for (RptAnnualReportEntity rptAnnualReportEntity : rptAnnualReportEntityList) {
                Map<String, Object> map = new HashMap<>();
                map.put("id", rptAnnualReportEntity.getId());
                map.put("enterprise_name", rptAnnualReportEntity.getEnterpriseName());
                map.put("social_credit_code", rptAnnualReportEntity.getSocialCreditCode());
                map.put("registration_number", rptAnnualReportEntity.getRegistrationNumber());
                map.put("registration_authority", rptAnnualReportEntity.getRegistrationAuthority());
                map.put("domination", rptAnnualReportEntity.getDomination());
                map.put("address", rptAnnualReportEntity.getAddress());
                map.put("contact_number", rptAnnualReportEntity.getContactNumber());
                map.put("establish_date", rptAnnualReportEntity.getEstablishDate());
                map.put("approval_date", rptAnnualReportEntity.getApprovalDate());
                map.put("enterprise_status", rptAnnualReportEntity.getEnterpriseStatus());
                map.put("liaison_man_name", rptAnnualReportEntity.getLiaisonManName());
                map.put("liaison_man_phone", rptAnnualReportEntity.getLiaisonManPhone());
                map.put("create_time", rptAnnualReportEntity.getCreateTime());
                map.put("report_status", rptAnnualReportEntity.getReportStatus());
                map.put("enterprise_type", rptAnnualReportEntity.getEnterpriseType());
                map.put("matching", rptAnnualReportEntity.getMatching());
                list.add(map);
            }
            //Map<String,Object> map = yyy;
            excelWriter.fill(list, writeSheet);
            //excelWriter.fill(map, writeSheet);
            excelWriter.finish();
            bos.flush();

        } catch (Exception e) {

        }
    }

    //替换模板row区域的${}值
    private LinkedHashMap<Integer, LinkedList<String>> linkedHashMapRows(List<Map<String, Object>> listExcel) {

        LinkedHashMap<Integer, LinkedList<String>> rows = new LinkedHashMap<Integer, LinkedList<String>>();
        for (int i = 0; i < listExcel.size(); i++) {
            LinkedList<String> row = new LinkedList<String>();
            Map<String, Object> map = listExcel.get(i);
            row.add(String.valueOf(map.get("id")));//序号
            row.add(String.valueOf(map.get("enterprise_name")));//企业名称
            row.add(String.valueOf(map.get("social_credit_code")));//统一社会信用代码
            row.add(String.valueOf(map.get("registration_number")));//注册号
            row.add(String.valueOf(map.get("registration_authority")));//登录机关
            row.add(String.valueOf(map.get("domination")));//管辖机关
            row.add(String.valueOf(map.get("address")));//地址
            row.add(String.valueOf(map.get("contact_number")));//联系电话
            row.add(String.valueOf(map.get("establish_date")));//成立日期
            row.add(String.valueOf(map.get("approval_date")));//核准日期
            row.add(String.valueOf(map.get("enterprise_status")));//企业状态
            row.add(String.valueOf(map.get("liaison_man_name")));//联络员姓名
            row.add(String.valueOf(map.get("liaison_man_phone")));//联络员电话
            row.add(String.valueOf(map.get("create_time")));//当前创建时间
            row.add(String.valueOf(map.get("report_status")));//数据上报状态
            row.add(String.valueOf(map.get("enterprise_type")));//企业类型
            row.add(String.valueOf(map.get("matching")));//匹配状态
            rows.put((i + 1), row);
        }
        return rows;
    }


}
